﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySite.Model;
using MySite.Enum;
using MySite.SQLHelper;
using System.Data;
using System.Data.OleDb;

namespace MySite.DAL
{
    public class VoteItemDAL : IDAL<VoteItemInfo>
    {
        public TableName tableName = TableName.VoteItem;
        public static bool TableExists = false;
        public readonly string InitTableSQL = @"Create table VoteItem(PID AUTOINCREMENT,VoteID int,VoteTitle Varchar(255),Position int,[Text] Varchar(255),VoteCount int,VoteCountPercent float)";

        public VoteItemDAL()
        {
            UseTable();
        }

        #region IDAL<VoteItemInfo> 成员

        public ReturnMessage Add(VoteItemInfo info)
        {

            ReturnMessage rm = new ReturnMessage(true);

            try
            {
                StringBuilder sb = new StringBuilder();
                MySite.Helper.Helper.TSQLForInsertIntoAccess<VoteItemInfo>(ref sb, info, tableName.ToString());
                rm = MySite.Helper.Helper.ExecTSQL(sb, OledbHelper.OlecdConn);
            }
            catch(Exception e)
            {
                rm.IsSuccessed = false;
                rm.Exception = e;
            }

            return rm;
        }

        public ReturnMessage Update(VoteItemInfo info)
        {
            ReturnMessage rm = new ReturnMessage(true);

            try
            {
                StringBuilder sb = new StringBuilder();
                MySite.Helper.Helper.TSQLForUpdateAccess<VoteItemInfo>(ref sb, info, tableName.ToString(), info.PID.ToString());
                MySite.Helper.Helper.ExecTSQL(sb, OledbHelper.OlecdConn);
            }
            catch (Exception e)
            {
                rm.IsSuccessed = false;
                rm.Exception = e;
            }

            return rm;
        }

        public ReturnMessage Delete(string PID)
        {
            ReturnMessage rm = new ReturnMessage();

            string sql = string.Format("delete from [{0}] where PID = {1}",tableName,PID);

            try
            {
                int o = OledbHelper.ExecuteNonQuery(OledbHelper.ConnectionStringCore, CommandType.Text, sql, null);

                rm.IsSuccessed = true;
            }
            catch(Exception e)
            {
                rm.IsSuccessed = false;
                rm.Exception = e;
            }

            return rm;
        }

        public IList<VoteItemInfo> Query(string where)
        {
            return Query(where,20);
        }

        public IList<VoteItemInfo> Query(string where, int pageSize, int pageIndex, string orderby, ref int pageCount)
        {
            IList<VoteItemInfo> result = new List<VoteItemInfo>();

            StringBuilder sql = new StringBuilder();

            StringBuilder sqlchildren = new StringBuilder();

            StringBuilder sqlCount = new StringBuilder();

            sql.AppendFormat("select top {0} * from [{1}] ", pageSize, tableName);
            sqlchildren.AppendFormat("select top {0} ID from [{1}] ", (pageIndex - 1) * pageSize, tableName);

            sqlCount.AppendFormat("select count(*) from [{0}] ", tableName);

            if (!string.IsNullOrEmpty(where))
            {
                sqlchildren.AppendFormat(" where {0} ", where);
                sqlCount.AppendFormat(" where {0} ", where);
            }

            if (!string.IsNullOrEmpty(orderby))
            {
                sqlchildren.AppendFormat(" order by {0} ", orderby);
            }

            if (string.IsNullOrEmpty(where) && pageIndex - 1 > 0)
            {
                sql.AppendFormat(" where ID not in ({0})", sqlchildren);
            }
            else if (pageIndex - 1 > 0)
            {
                sql.AppendFormat(" and ID not in ({0})", sqlchildren);
            }


            if (!string.IsNullOrEmpty(orderby))
            {
                sql.AppendFormat(" order by {0} ", orderby);
            }

            DataSet ds = OledbHelper.ExecuteQuery(OledbHelper.ConnectionStringCore, System.Data.CommandType.Text, sql.ToString(), null);

            if (ds != null && ds.Tables.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    VoteItemInfo ai = new VoteItemInfo();
                    Helper.Helper.GeneralModelInfo(dr, ref ai);
                    result.Add(ai);
                }
            }

            object o = Convert.ToInt32(OledbHelper.ExecuteScalar(OledbHelper.ConnectionStringCore, System.Data.CommandType.Text, sqlCount.ToString(), null));

            if (o != null)
            {
                if (Convert.ToInt32(o) % pageSize != 0)
                {
                    pageCount = Convert.ToInt32(o) / pageSize + 1;
                }
            }

            return result;
        }

        public int GetTotalCount(string where)
        {
            StringBuilder sqlCount = new StringBuilder();
            sqlCount.AppendFormat("select count(*) from [{0}] ",tableName);
            if (!string.IsNullOrEmpty(where))
            {
                sqlCount.AppendFormat(" where {0}", where);
            }
            object o = Convert.ToInt32(OledbHelper.ExecuteScalar(OledbHelper.ConnectionStringCore, System.Data.CommandType.Text, sqlCount.ToString(), null));

            return Convert.ToInt32(o);
        }

        #endregion

        #region IDAL<VoteItemInfo> 成员


        public ReturnMessage Exist(VoteItemInfo info)
        {
            ReturnMessage rm = new ReturnMessage();

            VoteItemInfo si = TakeOne(string.Format("[Text]='{0}'", info.Text));

            if (si != null)
            {
                rm.IsSuccessed = true;
                rm.ReturnData.Add("PID", si.PID);
            }
            else
            {
                rm.IsSuccessed = false;
            }

            return rm;
        }

        #endregion

        #region 检查表是否存在,不存在则创建
        /// <summary>
        /// 检查表是否存在,不存在则创建
        /// </summary>
        public void UseTable()
        {
            if (TableExists)
            {
                return;
            }

            if (!OledbHelper.CheckTable(tableName.ToString()))
            {
                using (var tempConn = OledbHelper.OlecdConn)
                {
                    tempConn.Open();
                    OleDbCommand cmd = new OleDbCommand(InitTableSQL, tempConn);
                    cmd.ExecuteNonQuery();
                    tempConn.Close();
                }
            }

            TableExists = true;
        }
        #endregion


        public VoteItemInfo TakeOne(string where)
        {
            return Query(where,1).FirstOrDefault();
        }


        public IList<VoteItemInfo> Query(string where, int top)
        {
            IList<VoteItemInfo> result = new List<VoteItemInfo>();

            string sql = string.Format("select top {0} * from [{1}]",top,tableName);

            if (!string.IsNullOrEmpty(where))
            {
                sql += " where " + where;
            }

            DataSet ds = OledbHelper.ExecuteQuery(OledbHelper.ConnectionStringCore, CommandType.Text, sql, null);

            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    VoteItemInfo si = new VoteItemInfo();
                    Helper.Helper.GeneralModelInfo(dr, ref si);
                    result.Add(si);
                }
            }

            return result;
        }
    }
}
